package com.hsnn.medstgmini.base.std.dao.impl;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.DicExperttypeDao;
import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.base.dict.model.DicExperttype;
import com.hsnn.medstgmini.base.std.dao.StdExpertDao;
import com.hsnn.medstgmini.base.std.model.StdExpert;
import com.hsnn.medstgmini.base.std.model.StdExpertLog;
import com.hsnn.medstgmini.dao.impl.HibernateSqlUtilImpl;
import com.hsnn.medstgmini.model.Status;
import com.hsnn.medstgmini.pro.project.dao.BidExpertExtractRuleDao;
import com.hsnn.medstgmini.sys.model.SysUser;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseDate;

/**
 * @category 专家库管理
 * @author kee
 * @date 2015-06-09
 */
@Repository
public class StdExpertDaoImpl extends BaseHibernateDAO  implements  StdExpertDao {
	
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	
	@Autowired
	private BidExpertExtractRuleDao expertExtractDao;
	
	@Autowired
	private DicExperttypeDao dicExperttypeDao;
	
	@Autowired
	private HibernateSqlUtilImpl dao;

	/**
	 * 
	 *@category 查询专家库列表
	 *@author yanke
	 *@date 2015年6月9日
	 */
	@Override
	public Pagination getStdExpertList(Pagination page) {
		Map<String,String> params = new HashMap<String,String>();
		String expSkillsType1 = page.getConditions().get("expSkillsType1");
		String expSkillsType2 = page.getConditions().get("expSkillsType2");
		String expSkillsType3 = page.getConditions().get("expSkillsType3");
		
		String name = page.getConditions().get("name");
		String companyId = page.getConditions().get("companyId");
		String status = page.getConditions().get("status");
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT s.expert_id expertId,s.expert_code expertCode,s.name name, de.expert_type_name expSkills,de.father_experttype_id,de.expert_type_code,  ")
		.append(" s.sex sex, DATE_FORMAT(s.birthday,'%Y-%m-%d') birthday,s.handphone handphone,s.company_id companyId, ")
		.append(" s.position position,s.status status FROM std_expert s left join dic_experttype de on de.dic_experttype_id=s.exp_skills WHERE 1=1 ");
		
		DicExperttype de=null;
		if(StringUtils.isNotBlank(expSkillsType1) && StringUtils.isNotBlank(expSkillsType2) && StringUtils.isNotBlank(expSkillsType3)){
			sql.append(" and de.expert_type_code like :expSkills ");
			de = expertExtractDao.getDicExperttypeById(expSkillsType3);
		}else if(StringUtils.isNotBlank(expSkillsType1) && StringUtils.isNotBlank(expSkillsType2) && StringUtils.isBlank(expSkillsType3)){
			sql.append(" and de.expert_type_code like :expSkills ");
			de = expertExtractDao.getDicExperttypeById(expSkillsType2);
		}else if(StringUtils.isNotBlank(expSkillsType1) && StringUtils.isBlank(expSkillsType2) && StringUtils.isBlank(expSkillsType3)){
			sql.append(" and de.expert_type_code like :expSkills ");
			de = expertExtractDao.getDicExperttypeById(expSkillsType1);
		}
		if(de!=null){
			params.put("expSkills",de.getExpertTypeCode()+"%");	
		}

		if(StringUtils.isNotBlank(name)){
			sql.append(" and s.name like :name ");
			params.put("name", "%"+name.trim()+"%");
		}
		if(StringUtils.isNotBlank(companyId)){
			sql.append(" and s.company_id like :companyId ");
			params.put("companyId", "%"+companyId.trim()+"%");
		}
		if(StringUtils.isNotBlank(status)){
			sql.append(" and s.status = :status ");
			params.put("status", status);
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	

	/**
	 * 
	 *@category 根据id查询专家库信息
	 *@author yanke
	 *@date 2015年6月9日
	 */
	public StdExpert getStdExpertById(String id) {
		Session session = sessionFactory.getCurrentSession();
		Criteria criteria = session.createCriteria(StdExpert.class);
		criteria.add(Restrictions.eq("expertId", id));
		return (StdExpert) criteria.uniqueResult();
	}

	/**
	 * 
	 *@category 保存专家库信息
	 *@author yanke
	 *@date 2015年6月9日
	 */
	public void saveStdExpert(StdExpert stdExpert) {
		Session session = sessionFactory.getCurrentSession();
		if (stdExpert.getExpertId() != null && !"".equals(stdExpert.getExpertId())) {
				StdExpert oldStdExpert = getStdExpertById(stdExpert.getExpertId());
				stdExpert.setStatus(oldStdExpert.getStatus());
				session.evict(oldStdExpert);
				session.update(stdExpert);
			} else {
				stdExpert.setExpertId(UUID.randomUUID().toString());
				stdExpert.setStatus(Status.enabled.getKey());
				session.save(stdExpert);
				session.flush();
			}
			writeLog(stdExpert);
			session.flush();
		
		
	}

	/**
	 * 
	 *@category 记录日志
	 *@author yanke
	 *@date 2015年6月9日
	 */
	private void writeLog(StdExpert stdExpert) {
		Session session = sessionFactory.getCurrentSession();
		StdExpert se = getStdExpertById(stdExpert.getExpertId());
		if(se != null){
			StdExpertLog stdExpertLog =new StdExpertLog();
			stdExpertLog.setLogId(UUID.randomUUID().toString());
			BeanUtils.copyProperties(se, stdExpertLog);
			session.save(stdExpertLog);
		}
	}

	/**
	 * 
	 *@category 查询专家库维护记录列表
	 *@author yanke
	 *@date 2015年6月9日
	 *List<StdExpertLog>
	 */
	@SuppressWarnings("unchecked")
	public List<Object> getStdExpertLogList(Pagination page,StdExpertLog stdExpertLog) {
		Session session = sessionFactory.getCurrentSession();
		Map<String, String> conditions = page.getConditions();
		// 查询总记录数
		StringBuffer sb = new StringBuffer();
		sb.append("select count(*) from StdExpertLog s where s.expertId = :expertId ");
		
		if (null != conditions && conditions.size() > 0) {
			if (null != conditions.get("startTime")
					&& !"".equals(conditions.get("startTime").trim())) {
				sb.append(" and s.updDatetime >=:startTime ");
			}
			if (null != conditions.get("endTime")
					&& !"".equals(conditions.get("endTime").trim())) {
				sb.append(" and s.updDatetime <=:endTime ");
			}
		}
		
		Query query = session.createQuery(sb.toString());
		query.setString("expertId",stdExpertLog.getExpertId());
		if (null != conditions && conditions.size() > 0) {
			if (null != conditions.get("startTime")
					&& !"".equals(conditions.get("startTime").trim())) {
				query.setTimestamp("startTime", ParseDate.fromFullFormat(conditions.get("startTime").trim()));
			}
			if (null != conditions.get("endTime")
					&& !"".equals(conditions.get("endTime").trim())) {
				query.setTimestamp("endTime", ParseDate.fromFullFormat(conditions.get("endTime").trim()));
			}
		}
		Long total = (Long) query.uniqueResult();
		page.setRecords(total);
		// 查询分页数据
		sb.setLength(0);
		sb.append("select s.log_id,s.detail,s.expert_code,s.name, s.sex,s.exp_skills, ");
		sb.append(" s.position,s.company_id,s.handphone,(DATE_FORMAT(s.birthday,'%Y-%m-%d'))birthday,s.status, ");
		sb.append(" u.name upd_user,(DATE_FORMAT(s.upd_datetime,'%Y-%m-%d %H:%i:%S'))upd_datetime ");
		sb.append(" from std_expert_log s  ");
		sb.append(" left join sys_user u on s.upd_user=u.id ");
		sb.append(" where s.expert_id = :expertId ");
		// 添加搜索条件
		if (null != conditions && conditions.size() > 0) {
			if (null != conditions.get("startTime")
					&& !"".equals(conditions.get("startTime").trim())) {
				sb.append(" and s.upd_datetime >=:startTime ");
			}
			if (null != conditions.get("endTime")
					&& !"".equals(conditions.get("endTime").trim())) {
				sb.append(" and s.upd_datetime <=:endTime ");
			}
		}
		sb.append(" order by s.upd_datetime desc ");
		query= session.createSQLQuery(sb.toString());
		// 搜索条件赋值
		query.setString("expertId",stdExpertLog.getExpertId());
		if (null != conditions && conditions.size() > 0) {
			if (null != conditions.get("startTime")
					&& !"".equals(conditions.get("startTime").trim())) {
				query.setTimestamp("startTime", ParseDate.fromFullFormat(conditions.get("startTime").trim()));
			}
			if (null != conditions.get("endTime")
					&& !"".equals(conditions.get("endTime").trim())) {
				query.setTimestamp("endTime", ParseDate.fromFullFormat(conditions.get("endTime").trim()));
			}
		}
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		return query.list();
		
	}

	/**
	 * 
	 *@category 停用启用（状态改变）
	 *@author yanke
	 *@date 2015年9月21日
	 *void
	 */
	@Override
	public void changeStatus(StdExpert stdExpert) {
		Session session = getSession();
		StringBuffer sql = new StringBuffer();
		sql.append("update std_expert  set ")
		  .append(" status=:status,upd_datetime=:updDatetime,upd_user=:updUser ")
		  .append(" where expert_id= :expertId ");
		Query update = session.createSQLQuery(sql.toString());
		update.setLong("status",stdExpert.getStatus());
		update.setTimestamp("updDatetime", new Date());
		update.setString("updUser", stdExpert.getUpdUser());
		update.setString("expertId", stdExpert.getExpertId());
		update.executeUpdate();
		session.flush();
		writeLog(stdExpert.getExpertId());
	}

	





	/**
	 * 
	 *@category 查询维护记录修改前和修改后的记录
	 *@author yanke
	 *@date 2015年6月9日
	 *List<StdExpert>
	 */
	@SuppressWarnings("unchecked")
	public List<StdExpertLog> getStdExpertCompareList(StdExpertLog stdExpertLog) {
		Session session = sessionFactory.getCurrentSession();
		Criteria criteria = session.createCriteria(StdExpertLog.class);
		criteria.add(Restrictions.eq("logId", stdExpertLog.getLogId()));
		stdExpertLog = (StdExpertLog) criteria.uniqueResult();
		if(stdExpertLog != null){
			criteria = session.createCriteria(StdExpertLog.class);
			criteria.add(Restrictions.le("updDatetime",stdExpertLog.getUpdDatetime()));
			criteria.add(Restrictions.eq("logId", stdExpertLog.getLogId()));
			criteria.addOrder(Order.desc("updDatetime"));
			criteria.setFirstResult(0);
			criteria.setMaxResults(2);
		}
		return criteria.list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<StdExpert> getStdExpertListByExpertCode(String expertCode) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from std_expert where 1 = 1");
		if(null!= expertCode && !"".equals(expertCode)){
			sql.append(" and expert_code = :expertCode");
		}
		Query query = sessionFactory.getCurrentSession().createSQLQuery(sql.toString())
									.addEntity(StdExpert.class);
		query.setParameter("expertCode", expertCode);
		return query.list();
	}


	/**
	 * 
	 *@category 查询维护记录详情
	 *@author yanke
	 *@date 2015年6月9日
	 *List<StdExpert>
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getStdExpertLogDetail(Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		String logId = page.getConditions().get("logId");
		StringBuffer  sql = new StringBuffer();
		sql.append(" SELECT CASE s.retired when '1' then '未退休' else '退休' end retiredTag, DATE_FORMAT(s.upd_datetime,'%Y-%m-%d %H:%i:%S') updDatetime, ")
		   .append(" DATE_FORMAT(s.birthday,'%Y-%m-%d') birthDay,s.*,b.name updUser ")
		   .append(" FROM std_expert_log s  left join sys_user b on b.id=s.upd_user ")
		   .append(" WHERE s.expert_id = (SELECT expert_id FROM std_expert_log WHERE log_id = :logId)  ")
		   .append(" AND s.upd_datetime <= (SELECT upd_datetime FROM std_expert_log where log_id = :logId)   ")
		   .append(" order by s.upd_datetime desc limit 2");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("logId", logId);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}


	/**
	 * 
	 *@category 专家库详情
	 *@author yanke
	 *@date 2015年9月21日
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getStdExpertDetail(Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		String expertId = page.getConditions().get("expertId");
		StringBuffer  sql = new StringBuffer();
		sql.append(" SELECT s.expert_id expertId,s.expert_code expertCode,s.name name,s.sex sex,s.id_num idNum,s.political_status politicalStatus, ")
		   .append(" DATE_FORMAT(s.birthday,'%Y-%m-%d') birthday,s.edu_bg eduBg,s.edu_degree eduDegree,s.tech_pos techPos,s.tech_pos_cert_no techPosCertNo, ")
		   .append(" s.position position,s.industry industry,s.profession profession, s.profes_exper profes_exper,s.pract_license1 practLicense1, ")
		   .append(" s.pract_license_cert_no1 practLicenseCertNo1, s.pract_license2 practLicense2,s.pract_license_cert_no2 practLicenseCertNo2, ")
		   .append(" s.pract_license3 practLicense3,s.pract_license_cert_no3 practLicenseCertNo3, s.pract_license4 practLicense4,s.pract_license_cert_no4 practLicenseCertNo4, ")
		   .append(" s.pract_license5 practLicense5,s.pract_license_cert_no5 practLicenseCertNo5,s.score_area1 scoreArea1,s.score_area2 scoreArea2,s.score_area3 scoreArea3, ")
		   .append(" s.area_id areaId,s.service_year serviceYear,s.retired retired,s.university university,s.major major,s.company_id companyId,s.org_id orgId, ")
		   .append(" s.company_addr companyAddr,s.home_addr homeAddr,s.handphone handphone,s.comp_phone compPhone,s.telephone telephone,s.zipcode zipcode,s.email email, ")
		   .append(" de.expert_type_name expSkills,s.score_yesrs scoreYesrs,s.remark remark,s.status status ")
		   .append(" FROM std_expert s left join dic_experttype de on de.dic_experttype_id=s.exp_skills WHERE s.expert_id =  :expertId ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("expertId", expertId);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}


	/**
	 * 
	 *@category 批量停用启用（状态改变）
	 *@author yanke
	 *@date 2015年6月9日
	 *void
	 */
	public int batchChangeStatus(StdExpert stdExpert) {
		Session session = getSession();
		StringBuffer sql = new StringBuffer();
		sql.append("update std_expert  set ")
		  .append(" status=:status,upd_datetime=:updDatetime,upd_user=:updUser ")
		  .append(" where expert_id= :expertId and status <> :status");
		Query update = session.createSQLQuery(sql.toString());
		update.setLong("status",stdExpert.getStatus());
		update.setTimestamp("updDatetime", new Date());
		update.setString("updUser", stdExpert.getUpdUser());
		update.setString("expertId", stdExpert.getExpertId());
		int count = update.executeUpdate();
		session.flush();
		if(count>0){
			writeLog(stdExpert.getExpertId());
		}
		return count;
	}

	/**
	 * 
	 *@category 写日志（新增、编辑、启用、停用 操作后将操作记录写入日志维护记录）
	 *@author yanke
	 *@date 2015年9月21日
	 *Pagination
	 */
	@Override
	public void writeLog(String expertId) {
		Session session = getSession();
		StringBuffer sql = new StringBuffer();
		sql.append("insert into std_expert_log select UUID(),t.*,:detail from std_expert t where t.expert_id= :expertId  ");
		Query query=session.createSQLQuery(sql.toString());
		query.setString("expertId", expertId);
		query.setString("detail", getDetail(expertId));
		query.executeUpdate();
		session.flush();
	}

	/**
	 * 新增和编辑
	 * @param page
	 */
	public void addAndEdit(StdExpert stdExpert, SysUser user) {
		if (stdExpert != null) {
			String expertId = stdExpert.getExpertId();
			if(StringUtils.isNoneBlank(expertId)){
				updateStdExpert(stdExpert,user);
			}else{
				insertStdExpert(stdExpert,user);
			}
		}
	}

	
	/**
	 * 编辑
	 * @param page
	 */
	public void updateStdExpert(StdExpert stdExpert, SysUser user) {
		Session session = sessionFactory.getCurrentSession();
			StringBuffer sql = new StringBuffer();
			sql.append(" update std_expert set   ");
			sql.append(" expert_code =:expertCode, name =:name ,sex= :sex, id_num = :idNum, political_status= :politicalStatus, birthday= :birthday, ");
			sql.append(" edu_bg= :eduBg, edu_degree= :eduDegree, tech_pos= :techPos, tech_pos_cert_no= :techPosCertNo, position= :position, ");
			sql.append(" industry= :industry, profession= :profession, profes_exper= :profesExper, pract_license1= :practLicense1, ");
			sql.append(" pract_license_cert_no1= :practLicenseCertNo1, pract_license2= :practLicense2, pract_license_cert_no2= :practLicenseCertNo2, ");
			sql.append(" pract_license3= :practLicense3, pract_license_cert_no3= :practLicenseCertNo3, pract_license4= :practLicense4, ");
			sql.append(" pract_license_cert_no4= :practLicenseCertNo4, pract_license5= :practLicense5, pract_license_cert_no5= :practLicenseCertNo5, ");
			sql.append(" score_area1= :scoreArea1, score_area2= :scoreArea2, score_area3= :scoreArea3, area_id= :areaId , service_year= :serviceYear, ");
			sql.append(" retired= :retired, university= :university, major= :major, company_id= :companyId, org_id= :orgId, company_addr= :companyAddr, ");
			sql.append(" home_addr= :homeAddr, handphone= :handphone, comp_phone= :compPhone, telephone= :telephone, zipcode= :zipcode, email= :email, ");
			sql.append(" exp_skills= :expSkills, score_yesrs= :scoreYesrs, remark= :remark, upd_datetime= sysdate(), upd_user= :updUser ");
			sql.append("  where expert_id = :expertId");
			Query query = session.createSQLQuery(sql.toString());
			
			query.setString("expertCode", stdExpert.getExpertCode());
			query.setString("name", stdExpert.getName());
			query.setString("sex", stdExpert.getSex());
			query.setString("idNum", stdExpert.getIdNum());
			query.setString("politicalStatus", stdExpert.getPoliticalStatus());
			query.setDate("birthday", stdExpert.getBirthday());
			query.setString("eduBg", stdExpert.getEduBg());
			query.setString("eduDegree", stdExpert.getEduDegree());
			query.setString("techPos", stdExpert.getTechPos());
			query.setString("techPosCertNo", stdExpert.getTechPosCertNo());
			query.setString("position", stdExpert.getPosition());
			query.setString("industry", stdExpert.getIndustry());
			query.setString("profession", stdExpert.getProfession());
			query.setInteger("profesExper", stdExpert.getProfesExper());
			query.setString("practLicense1", stdExpert.getPractLicense1());
			query.setString("practLicenseCertNo1", stdExpert.getPractLicenseCertNo1());
			query.setString("practLicense2", stdExpert.getPractLicense2());
			query.setString("practLicenseCertNo2", stdExpert.getPractLicenseCertNo2());
			query.setString("practLicense3", stdExpert.getPractLicense3());
			query.setString("practLicenseCertNo3", stdExpert.getPractLicenseCertNo3());
			query.setString("practLicense4", stdExpert.getPractLicense4());
			query.setString("practLicenseCertNo4", stdExpert.getPractLicenseCertNo4());
			query.setString("practLicense5", stdExpert.getPractLicense5());
			query.setString("practLicenseCertNo5", stdExpert.getPractLicenseCertNo5());
			query.setString("scoreArea1", stdExpert.getScoreArea1());
			query.setString("scoreArea2", stdExpert.getScoreArea2());
			query.setString("scoreArea3", stdExpert.getScoreArea3());
			query.setString("areaId", stdExpert.getAreaId());
			query.setString("serviceYear", stdExpert.getServiceYear());
			query.setInteger("retired", stdExpert.getRetired());
			query.setString("university", stdExpert.getUniversity());
			query.setString("major", stdExpert.getMajor());
			query.setString("companyId", stdExpert.getCompanyId());
			query.setString("orgId", stdExpert.getOrgId());
			query.setString("companyAddr", stdExpert.getCompanyAddr());
			query.setString("homeAddr", stdExpert.getHomeAddr());
			query.setString("handphone", stdExpert.getHandphone());
			query.setString("compPhone", stdExpert.getCompPhone());
			query.setString("telephone", stdExpert.getTelephone());
			query.setString("zipcode", stdExpert.getZipcode());
			query.setString("email", stdExpert.getEmail());
			query.setString("expSkills", stdExpert.getExpSkills());
			query.setString("scoreYesrs", stdExpert.getScoreYesrs());
			query.setString("remark", stdExpert.getRemark());
			query.setString("updUser", stdExpert.getUpdUser());
			query.setString("expertId", stdExpert.getExpertId());
			query.executeUpdate();
			session.flush();
			writeLog(stdExpert.getExpertId());
		
		
	}

	/**
	 * 新增
	 * @param page
	 */
	public void insertStdExpert(StdExpert stdExpert, SysUser user) {
		Session session = sessionFactory.getCurrentSession();
		String expertId = UUID.randomUUID().toString();
			StringBuffer sql = new StringBuffer();
			sql.append(" insert into std_expert ")
			   .append(" (expert_id,expert_code,name,sex,id_num,political_status,birthday,edu_bg,edu_degree,tech_pos,tech_pos_cert_no,position, ")
			   .append(" industry,profession,profes_exper,pract_license1,pract_license_cert_no1,pract_license2,pract_license_cert_no2, ")
			   .append(" pract_license3,pract_license_cert_no3,pract_license4,pract_license_cert_no4,pract_license5,pract_license_cert_no5, ")
			   .append(" score_area1,score_area2,score_area3,area_id,service_year,retired,university,major,company_id,org_id,company_addr,home_addr, ")
			   .append(" handphone,comp_phone,telephone,zipcode,email,exp_skills,score_yesrs,remark,status,upd_datetime,upd_user) ")
			   .append(" values  ")
			   .append(" (:expertId,:expertCode,:name,:sex,:idNum,:politicalStatus,:birthday,:eduBg,:eduDegree,:techPos,:techPosCertNo, ")
			   .append(" :position,:industry,:profession,:profesExper,:practLicense1,:practLicenseCertNo1,  ")
			   .append(" :practLicense2,:practLicenseCertNo2,:practLicense3,:practLicenseCertNo3,:practLicense4,:practLicenseCertNo4, ")
			   .append(" :practLicense5,:practLicenseCertNo5,:scoreArea1,:scoreArea2,:scoreArea3,:areaId, ")
			   .append(" :serviceYear,:retired,:university,:major,:companyId,:orgId,  ")
			   .append(" :companyAddr,:homeAddr,:handphone,:compPhone,:telephone,:zipcode, ")
			   .append(" :email,:expSkills,:scoreYesrs,:remark,1, :updDatetime,:updUser )  ");
			Query query = session.createSQLQuery(sql.toString());
			query.setString("expertId", expertId);
			query.setString("expertCode", stdExpert.getExpertCode());
			query.setString("name", stdExpert.getName());
			query.setString("sex", stdExpert.getSex());
			query.setString("idNum", stdExpert.getIdNum());
			query.setString("politicalStatus", stdExpert.getPoliticalStatus());
			query.setString("birthday", stdExpert.getBirthdayStr());
			query.setString("eduBg", stdExpert.getEduBg());
			query.setString("eduDegree", stdExpert.getEduDegree());
			query.setString("techPos", stdExpert.getTechPos());
			query.setString("techPosCertNo", stdExpert.getTechPosCertNo());
			query.setString("position", stdExpert.getPosition());
			query.setString("industry", stdExpert.getIndustry());
			query.setString("profession", stdExpert.getProfession());
			query.setInteger("profesExper", stdExpert.getProfesExper());
			query.setString("practLicense1", stdExpert.getPractLicense1());
			query.setString("practLicenseCertNo1", stdExpert.getPractLicenseCertNo1());
			query.setString("practLicense2", stdExpert.getPractLicense2());
			query.setString("practLicenseCertNo2", stdExpert.getPractLicenseCertNo2());
			query.setString("practLicense3", stdExpert.getPractLicense3());
			query.setString("practLicenseCertNo3", stdExpert.getPractLicenseCertNo3());
			query.setString("practLicense4", stdExpert.getPractLicense4());
			query.setString("practLicenseCertNo4", stdExpert.getPractLicenseCertNo4());
			query.setString("practLicense5", stdExpert.getPractLicense5());
			query.setString("practLicenseCertNo5", stdExpert.getPractLicenseCertNo5());
			query.setString("scoreArea1", stdExpert.getScoreArea1());
			query.setString("scoreArea2", stdExpert.getScoreArea2());
			query.setString("scoreArea3", stdExpert.getScoreArea3());
			query.setString("areaId", stdExpert.getAreaId());
			query.setString("serviceYear", stdExpert.getServiceYear());
			query.setInteger("retired", stdExpert.getRetired());
			query.setString("university", stdExpert.getUniversity());
			query.setString("major", stdExpert.getMajor());
			query.setString("companyId", stdExpert.getCompanyId());
			query.setString("orgId", stdExpert.getOrgId());
			query.setString("companyAddr", stdExpert.getCompanyAddr());
			query.setString("homeAddr", stdExpert.getHomeAddr());
			query.setString("handphone", stdExpert.getHandphone());
			query.setString("compPhone", stdExpert.getCompPhone());
			query.setString("telephone", stdExpert.getTelephone());
			query.setString("zipcode", stdExpert.getZipcode());
			query.setString("email", stdExpert.getEmail());
			query.setString("expSkills", stdExpert.getExpSkills());
			query.setString("scoreYesrs", stdExpert.getScoreYesrs());
			query.setString("remark", stdExpert.getRemark());
			query.setTimestamp("updDatetime",  new Date());
			query.setString("updUser", stdExpert.getUpdUser());
			query.executeUpdate();
			session.flush();
			writeLog(expertId);
		
	}



	/**
	 * 根据code或name查看有无重复数据
	 * @param code,name
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<StdExpert> getPackunitBy(String key, String value, String id) {
		StringBuffer hql = new StringBuffer();
		hql.append("from StdExpert where ")
		   .append(key)
		   .append(" = ? and expertId <> ?");
		Query query = getSession().createQuery(hql.toString());
		query.setParameter(0, value);
		query.setParameter(1,id);
		return query.list();
	}

	
	/**获取日志表字段detail的值
	 * @param code,name
	 * @return
	 */
	private String getDetail(String expertId){
		StringBuffer sb = new StringBuffer()
		.append("select a.expert_code,a.name,a.sex,a.id_num,a.political_status,DATE_FORMAT(a.birthday,'%Y-%m-%d'),a.edu_bg,a.edu_degree,")
		.append("a.tech_pos,a.tech_pos_cert_no,a.position,a.industry,a.profession,a.profes_exper,a.pract_license1,a.pract_license_cert_no1,")
		.append("a.pract_license2,a.pract_license_cert_no2,a.pract_license3,a.pract_license_cert_no3,a.pract_license4,a.pract_license_cert_no4,")
		.append("a.pract_license5,a.pract_license_cert_no5,a.score_area1,a.score_area2,a.score_area3,b.area_name,a.service_year,a.retired,a.university,")
		.append("a.major,a.company_id,a.org_id,a.company_addr,a.home_addr,a.handphone,a.comp_phone,a.telephone,a.zipcode,a.email,a.exp_skills,"
				+ "a.score_yesrs,a.remark,a.status ");
		
		StringBuffer sql = new StringBuffer()
		.append(sb.toString())
		.append(" from std_expert a  left join dic_area b on b.area_id = a.area_id where a.expert_id = :expertId ")
		.append(" union all ")
		.append(" select t.* from ( ")
		.append(sb.toString())
		.append(" from std_expert_log a left join dic_area b on b.area_id = a.area_id  where a.expert_id = :expertId order by a.upd_datetime ")
		.append(" desc limit 1) t");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("expertId", expertId);
		List<?> list = dao.getSqlList(sql.toString(), params);
		
		StringBuffer detail = new StringBuffer();
		String[] colunm ={"编号","姓名","性别","身份证号码","政治面貌","出生日期","最高学历",
				"最高学位","职称","职称证书号","职务","所属行业","从事专业","从事专业年限","执业资格1",
				"执业资格证书号1","执业资格2","执业资格证书号2","执业资格3","执业资格证书号3","执业资格4",
				"执业资格证书号4","执业资格5","职业资格证书号5","评标专业1","评标专业2","评标专业3","地区",
				"工龄","是否退休","毕业院校","所学专业","工作单位","组织机构代码","单位地址","家庭地址","移动电话",
				"单位电话","住宅电话","邮政编码","电子邮箱","专家类别","评标经验","备注","状态"};
		if(list.size()==1){
			Object[] obj =(Object[]) list.get(0);
			obj[44] = obj[44]==null?"":"1".equals(obj[44].toString())?"启用":"停用";
			obj[29] = obj[29]==null?"":"1".equals(obj[29].toString())?"是":"否";
			for(int i = 0;i<obj.length;i++){
				detail.append(";").append(colunm[i]).append(":").append(obj[i]==null?"":obj[i].toString());
			}
		}else{
			Object[] obj1 =(Object[]) list.get(0);//修改后
			Object[] obj2 =(Object[]) list.get(1);//修改前
			obj1[44] = obj1[44]==null?"":"1".equals(obj1[44].toString())?"启用":"停用";
			obj2[44] = obj2[44]==null?"":"1".equals(obj2[44].toString())?"启用":"停用";
			obj1[29] = obj1[29]==null?"":"1".equals(obj1[29].toString())?"是":"否";
			obj2[29] = obj2[29]==null?"":"1".equals(obj2[29].toString())?"是":"否";
			for(int i = 0;i<obj1.length;i++){
				if(!(obj1[i]==null?"":obj1[i].toString()).equals(obj2[i]==null?"":obj2[i].toString())){
					if("专家类别".equals(colunm[i])){
						DicExperttype de2 = expertExtractDao.getDicExperttypeById(obj2[i].toString());
						DicExperttype de1 = expertExtractDao.getDicExperttypeById(obj1[i].toString());
						detail.append(";").append(colunm[i]).append(":")
						.append(de2==null?"":de2.getExpertTypeName())
						.append(",")
						.append(de1==null?"":de1.getExpertTypeName());//最新的
					}else{
						detail.append(";").append(colunm[i]).append(":").append(obj2[i]==null?"":obj2[i].toString()).append(",")
						.append(obj1[i]==null?"":obj1[i].toString());						
					}
				}
				
			}
		}
		detail.delete(0, 1);
		return detail.toString();
	}
	
	/**
	 * 根据条件获取不分页数据
	 * @param page
	 * @return page
	 */
	@Override
	public Pagination getStdExpertNoPage(Pagination page){
		Map<String,String> params = new HashMap<String, String>();
		String expSkills = page.getConditions().get("expSkills");
		String name = page.getConditions().get("name");
		String companyId = page.getConditions().get("companyId");
		String status = page.getConditions().get("status");
		StringBuffer sql = new StringBuffer()
		.append("	SELECT") 
		.append("	a.expert_code expertCode,a.name name,a.sex sex,a.exp_skills expSkills,a.id_num idNum,")
		.append("	a.political_status politicalStatus,DATE_FORMAT(a.birthday,'%Y-%m-%d') birthday,")
		.append("	a.edu_bg eduBg,a.edu_degree eduDegree,a.tech_pos techPos,a.tech_pos_cert_no techPosCertNo,")
		.append("	a.position position,a.industry industry,a.profession profession,a.profes_exper profesExper,")
		.append("	a.pract_license1 practLicense1,a.pract_license_cert_no1 practLicenseCertNo1,")
		.append("	a.pract_license2 practLicense2,a.pract_license_cert_no2 practLicenseCertNo2,")
		.append("	a.pract_license3 practLicense3,a.pract_license_cert_no3 practLicenseCertNo3,")
		.append("	a.pract_license4 practLicense4,a.pract_license_cert_no4 practLicenseCertNo4,")
		.append("	a.pract_license5 practLicense5,a.pract_license_cert_no5 practLicenseCertNo5,")
		.append("	a.score_area1 scoreArea1,a.score_area2 scoreArea2,a.score_area3 scoreArea3,")
		.append("	b.area_name areaId,a.service_year serviceYear,")
		.append("	CASE a.retired WHEN 1 THEN '否' WHEN 0 THEN '是' ELSE '' END retired,")
		.append("	a.university university,a.major major,a.company_id companyId,a.org_id orgId,")
		.append("	a.company_addr companyAddr,a.home_addr homeAddr,a.handphone handphone,")
		.append("	a.comp_phone compPhone,a.telephone telephone,a.zipcode zipcode,")
		.append("	a.email email,a.score_yesrs scoreYesrs,a.remark remark,")
		.append("	CASE a.status WHEN 2 THEN '停用' ELSE '启用' END status")
		.append("	FROM std_expert a LEFT JOIN dic_area b ON b.area_id = a.area_id") 
		.append("	WHERE 1 = 1 ");
		if(StringUtils.isNotBlank(expSkills)){
			sql.append(" and a.exp_skills = :expSkills ");
			params.put("expSkills", expSkills);
		}
		if(StringUtils.isNotBlank(name)){
			sql.append(" and a.name like :name ");
			params.put("name", "%"+name+"%");
		}
		if(StringUtils.isNotBlank(companyId)){
			sql.append(" and a.company_id like :companyId ");
			params.put("companyId", "%"+companyId+"%");
		}
		if(StringUtils.isNotBlank(status)){
			sql.append(" and a.status = :status ");
			params.put("status", status);
		}
		sql.append("	ORDER BY a.name ");
		page.setRows(dao.getSqlMapList(sql.toString(), params));
		return page;
	}

}
